<?php
/***************************************************************
*  Copyright notice
*  (c) 2009, jianyuzhu@gmail.com
*  All rights reserved
*  This script is part of the PPEMI project.
***************************************************************/

//
include_once(CONFIG_PATH . CONFIG_DIR_CLASSES . 'base.item.class.php');

class BaseItemItemsBase extends BaseItemBase {
	var $join_key = 'id';
	var $relation_key = 'id';
	
	//join
	var $table_join = '';
	var $table_join_key = '';
	var $table_join_params = '';
	var $table_join_fields = array();
	
	//relation
	var $table_relation = '';
	var $table_relation_key = '';
	var $table_relation_params = '';
	var $table_relation_fields = array();
	
	//joins
	//	table, key, fkey, params, fields
	//
	var $joins = array();
	
	//relations
	//	table, key, fkey, params, fields
	//		params
	//		2	=
	//		3	key
	//
	var $relations = array();
	
	//constructor
	function BaseItemItemsBase() {
		parent::BaseItemBase();
	}
	
	//methods
	function getItems($filter = '', $check = 0, $limit = true) {
		$query_field_str = '*';
		if( $this->list_fields == 'ALL' ) {
			$query_field_str = "*";
		} elseif( $this->list_fields == '*' ) {
			$query_field_str = "a.*";
		} elseif( is_array($this->list_fields) && sizeof($this->list_fields) > 0 ) {
			$query_field_str = implode(', a.', $this->list_fields);
		}
		
		$query_str = "";
		$query_str .= $this->_get_query_str($filter, $check);
		
		$query_order_str = "";
		if( $this->sort_order == true ) {
			if( is_array($this->sort_fields) ) {
				$query_order_str = " order by ";
				$i = 0;
				foreach($this->sort_fields as $k => $v) {
					if( substr($v, 0, 1) == '=' ) {
						$v = substr($v, 1);
					} else {
						$v = $this->prefix . $v;
					}
					if( substr($v, -1) == '-' ) {
						$as = 'desc';
						$v = substr($v, 0, -1);
					} elseif( substr($v, -1) == '+' ) {
						$as = 'asc';
						$v = substr($v, 0, -1);
					} else {
						$as = 'asc';
					}
					if( $i > 0 ) {
						$query_order_str .= ", ";
					}
					$query_order_str .= " a." . $v . " " . $as;
					$i++;
				}
			} else {
				$query_order_str = " order by a." . $this->prefix . "sort_order asc";
			}
		}
		
		$query_limit_str = "";
		if( $limit == true ) {
			$query_limit_str = $this->getQueryLimitStr();
		}
		
		//
		$query = "select " . $query_field_str . " ";
		if( $this->table_join != '' ) {
			if( $this->table_join_fields == '*' ) {
				$query .= ", b.*";
			} elseif( is_array($this->table_join_fields) ) {
				foreach($this->table_join_fields as $k => $v) {
					$query .= ", b." . $v;
				}
			}
		}
		if( $this->table_relation != '' ) {
			if( $this->table_relation_fields == '*' ) {
				$query .= ", c.*";
			} elseif( is_array($this->table_relation_fields) ) {
				foreach($this->table_relation_fields as $k => $v) {
					$query .= ", c." . $v;
				}
			}
		}
		if( is_array($this->joins) ) {
			foreach($this->joins as $jk => $join) {
				if( isset($join['fields']) ) {
					if( $join['fields'] == '*' ) {
						$query .= ", tj.*";
					} elseif( is_array($join['fields']) ) {
						foreach($join['fields'] as $k => $v) {
							$query .= ", tj" . $jk . "." . $v;
						}
					}
				}
			}
		}
		if( is_array($this->relations) ) {
			foreach($this->relations as $rk => $relation) {
				if( isset($relation['fields']) ) {
					if( $relation['fields'] == '*' ) {
						$query .= ", tr.*";
					} elseif( is_array($relation['fields']) ) {
						foreach($relation['fields'] as $k => $v) {
							$query .= ", tr" . $rk . "." . $v;
						}
					}
				}
			}
		}
		//
		$query .= " from " . $this->table . " a ";
		if( $this->table_join != '' ) {
			$query .= " left join " . $this->table_join . " b " . " on a." . $this->join_key . " = b." . $this->table_join_key . " ";
			if( is_array($this->table_join_params) ) {
				foreach($this->table_join_params as $k => $v) {
					$query_str .= " and b." . $k . " = '" . $v . "'";
				}
			}
		}
		if( is_array($this->joins) ) {
			foreach($this->joins as $jk => $join) {
				$query .= " left join " . $join['table'] . " tj" . $jk . " on a." . $this->prefix . $join['fkey'] . " = tj" . $jk . "." . $join['key'] . " ";
				if( $join['params'] != '' ) {
					if( is_array($join['params']) ) {
						foreach($join['params'] as $k => $v) {
							$query .= " and tj" . $jk . "." . $k . " = '" . $v . "' ";
						}
					}
				}
			}
		}
		if( $this->table_relation != '' ) {
			$query .= ", " . $this->table_relation . " c ";
		}
		if( is_array($this->relations) ) {
			foreach($this->relations as $rk => $relation) {
				$query .= ", " . $relation['table'] . " tr" . $rk . " ";
			}
		}
		$query .= " where " . $this->_query_owner_rt('a') 
					. $query_str . $query_order_str . $query_limit_str;
		$rows = $this->adb->getRows($query);
		
		//
		if( $this->format == true ) {
			for($i=0, $n=sizeof($rows); $i<$n; $i++) {
				$rows[$i] = $this->_post_field($rows[$i], '', $this->prefix, $this->format_check);
			}
		}
		
		return $rows;
	}
	
	function getItemsTotal($filter = '', $check = 0) {
		$query_str = "";
		$query_str .= $this->_get_query_str($filter, $check);
		
		//
		$query = "select distinct a." . $this->prefix . "id from " . $this->table . " a ";
		if( $this->table_join != '' ) {
			$query .= " left join " . $this->table_join . " b " . " on a." . $this->join_key . " = b." . $this->table_join_key . " ";
			if( is_array($this->table_join_params) ) {
				foreach($this->table_join_params as $k => $v) {
					$query_str .= " and b." . $k . " = '" . $v . "'";
				}
			}
		}
		if( is_array($this->joins) ) {
			foreach($this->joins as $jk => $join) {
				$query .= " left join " . $join['table'] . " tj" . $jk . " on a." . $this->prefix . $join['fkey'] . " = tj" . $jk . "." . $join['key'] . " ";
				if( $join['params'] != '' ) {
					if( is_array($join['params']) ) {
						foreach($join['params'] as $k => $v) {
							$query .= " and tj" . $jk . "." . $k . " = '" . $v . "' ";
						}
					}
				}
			}
		}
		if( $this->table_relation != '' ) {
			$query .= ", " . $this->table_relation . " c ";
		}
		if( is_array($this->relations) ) {
			foreach($this->relations as $rk => $relation) {
				$query .= ", " . $relation['table'] . " tr" . $rk . " ";
			}
		}
		$query .= " where " . $this->_query_owner_rt('a') 
					. $query_str;
		
		return $this->adb->getCount($query);
	}
	
	//private
	function _get_query_str($filter = '', $check = 0) {
		$query_str = "";
		if( $check == -1 ) {
			$query_str .= "";
		} elseif( $this->_table_status_usable == true ) {
			$query_str .= " and a." . $this->prefix . "status = 1";
		}
		if( is_array($filter) ) {
			$filter = func_db_input($filter);
			if( is_array($this->filter_query) ) {
				$filter = array_merge($filter, $this->filter_query);
			}
			if( is_array($this->filters) ) {
				foreach($this->filters as $k => $v) {
					$filter[$k] = $v;
				}
			}
			//
			if( isset($filter['keyword']) && $this->_not_null($filter['keyword']) == true ) {
				if( is_array($this->search_fields) ) {
					if( $this->search_type != 'or' ) {
						$this->search_type = 'and';
					}
					$i = 0;
					$query_str .= " and ( ";
					foreach($this->search_fields as $k => $field) {
						if( $i > 0 ) {
							$query_str .= " " . $this->search_type . " ";
						}
						$query_str .= " a." . $this->prefix . $field . " like '%" . $filter['keyword'] . "%' ";
						$i++;
					}
					$query_str .= " ) ";
				} elseif( is_string($this->search_fields) ) {
					$query_str .= " and a." . $this->prefix . $this->search_fields . " like '%" . $filter['keyword'] . "%' ";
				}
			}
			if( isset($filter['ids']) && $this->_not_null($filter['ids']) == true ) {
				if( is_array($filter['ids']) ) {
					$query_str .= " and a." . $this->prefix . "id in (" . $this->_implode(', ', $filter['ids']) . ") ";
				} else {
					$query_str .= " and a." . $this->prefix . "id = '" . (int)$filter['ids'] . "' ";
				}
			}
			if( isset($filter['category']) ) {
				if( is_array($filter['category']) ) {
					$query_str .= " and a.categories_id in (" . $this->_implode(', ', $filter['category']) . ") ";
				} else {
					$query_str .= " and a.categories_id = '" . (int)$filter['category'] . "' ";
				}
			}
			if( is_array($this->filter_fields) && sizeof($this->filter_fields) > 0 ) {
				$qt = $this->_get_query_type();
				foreach($filter as $k => $v) {
					if( is_array($this->filter_map) && isset($this->filter_map[$k]) ) {
						$k = $this->filter_map[$k];
					}
					if( in_array($k, $this->filter_fields) ) {
						$t = '';
						$p = 'a.' . $this->prefix;
						if( strpos($k, ':') !== false ) {
							$ks = explode(':', $k);
							$k = $ks['0'];
							$t = $ks['1'];
							if( sizeof($ks) > 2 ) {
								$p = $ks['2'];
							}
						}
						if( substr($k, 0, 1) == '=' ) {
							$k = substr($k, 1);
							$p = 'a.';
						}
						if( !in_array($t, $qt) ) {
							$t = '';
						}
						if( in_array($t, array('=', '>', '>=', '<', '<=', '!=')) ) {
							if( $v === 'now()' || $v === 'now' ) {
								$query_str .= " and " . $p . $k . " " . $t . " now() ";
							} else {
								$query_str .= " and " . $p . $k . " " . $t . " '" . $v . "' ";
							}
						} elseif( in_array($t, array('[]', '[)', '(]', '()')) ) {
							if( is_array($v) ) {
								$t1 = substr($t, 0, 1);
								$t2 = substr($t, 1, 1);
								if( $t1 == '[' ) {
									$t1 = '<=';
								} elseif( $t1 == '(' ) {
									$t1 = '<';
								}
								if( $t2 == '[' ) {
									$t2 = '<=';
								} elseif( $t1 == '(' ) {
									$t2 = '<';
								}
								
								$query_str .= " and " . $p . $k . " " . $t1 . " '" . $v['0'] . "' ";
								$query_str .= " and " . $p . $k . " " . $t2 . " '" . $v['1'] . "' ";
							}
						} elseif( in_array($t, array('like', 'notlike', '%like%', '%like', 'like%',)) ) {
							if( $t == 'notlike' ) {
								$query_str .= " and " . $p . $k . " not like '%" . $v . "%' ";
							} else {
								$query_str .= " and " . $p . $k . " like '%" . $v . "%' ";
							}
						} else {
							if( $v === 'now()' || $v === 'now' ) {
								$query_str .= " and " . $p . $k . " < now() ";
							} elseif( is_array($v) && sizeof($v) > 0 ) {
								$query_str .= " and " . $p . $k . " in ('" . implode("','", $v) . "') ";
							} elseif( is_int($v) ) {
								$query_str .= " and " . $p . $k . " = '" . $v . "' ";
							} elseif( is_numeric($v) ) {
								$query_str .= " and " . $p . $k . " = '" . $v . "' ";
							} elseif( is_string($v) ) {
								$query_str .= " and " . $p . $k . " like '%" . $v . "%' ";
							} else {
								$query_str .= " and " . $p . $k . " like '%" . $v . "%' ";
							}
						}
					}
				}
			}
		}
		//
		$query_str .= $this->_get_query_str_relation($filter, $check);
		//
		$query_str .= $this->_get_query_str_custom($filter, $check);
		
		return $query_str;
	}
	
	function _get_query_str_relation($filter = '', $check = 0) {
		$query_str = "";
		
		if( $this->table_relation != '' ) {
			$query_str .= " and a." . $this->relation_key . " = c." . $this->table_relation_key;
			if( is_array($this->table_relation_params) ) {
				foreach($this->table_relation_params as $k => $v) {
					$query_str .= " and c." . $k . " = '" . $v . "'";
				}
			}
		}
		if( is_array($this->relations) ) {
			$qt = $this->_get_query_type();
			foreach($this->relations as $rk => $relation) {
				$query .= " and a." . $relation['fkey'] . " = tr" . $rk . "." . $relation['key'] . " ";
				if( $relation['params'] != '' ) {
					if( is_array($relation['params']) ) {
						foreach($relation['params'] as $k => $v) {
							$t = '';
							$p = "tr" . $rk . ".";
							if( strpos($k, ':') !== false ) {
								$ks = explode(':', $k);
								$k = $ks['0'];
								$t = $ks['1'];
								if( sizeof($ks) > 2 ) {
									$p = $ks['2'];
								}
							}
							if( !in_array($t, $qt) ) {
								$t = '';
							}
							if( in_array($t, array('=', '>', '>=', '<', '<=', '!=')) ) {
								if( $v === 'now()' || $v === 'now' ) {
									$query_str .= " and " . $p . $k . " " . $t . " now() ";
								} else {
									$query_str .= " and " . $p . $k . " " . $t . " '" . $v . "' ";
								}
							} elseif( in_array($t, array('[]', '[)', '(]', '()')) ) {
								if( is_array($v) ) {
									$t1 = substr($t, 0, 1);
									$t2 = substr($t, 1, 1);
									if( $t1 == '[' ) {
										$t1 = '<=';
									} elseif( $t1 == '(' ) {
										$t1 = '<';
									}
									if( $t2 == '[' ) {
										$t2 = '<=';
									} elseif( $t1 == '(' ) {
										$t2 = '<';
									}
									
									$query_str .= " and " . $p . $k . " " . $t1 . " '" . $v['0'] . "' ";
									$query_str .= " and " . $p . $k . " " . $t2 . " '" . $v['1'] . "' ";
								}
							} elseif( in_array($t, array('like', 'notlike', '%like%', '%like', 'like%',)) ) {
								if( $t == 'notlike' ) {
									$query_str .= " and " . $p . $k . " not like '%" . $v . "%' ";
								} else {
									$query_str .= " and " . $p . $k . " like '%" . $v . "%' ";
								}
							} else {
								if( $v === 'now()' || $v === 'now' ) {
									$query_str .= " and " . $p . $k . " < now() ";
								} elseif( is_array($v) && sizeof($v) > 0 ) {
									$query_str .= " and " . $p . $k . " in ('" . implode("','", $v) . "') ";
								} elseif( is_int($v) ) {
									$query_str .= " and " . $p . $k . " = '" . $v . "' ";
								} elseif( is_numeric($v) ) {
									$query_str .= " and " . $p . $k . " = '" . $v . "' ";
								} elseif( is_string($v) ) {
									$query_str .= " and " . $p . $k . " like '%" . $v . "%' ";
								} else {
									$query_str .= " and " . $p . $k . " like '%" . $v . "%' ";
								}
							}
						}
					}
				}
			}
		}
		
		return $query_str;
	}
	
	function _get_query_str_custom($filter = '', $check = 0) {
		return "";
	}
}
//
?>